### What is SQL? SQL or structured query language is a declarative language used for relational databases. It is important to note that everything that is done in SQL could be done in other languages. SQL has been designed to manipulate data more easily. One SQL line can replace many lines of python. Here also, we will not spend time on the syntax but rather on some interesting concepts that we covered in the course. ##### CRUD The main actions that we make on a database are : C: Create / INSERT R: Read / SELECT U: Update / ALTER D: Delete / DROP ##### Primary key, foreign key and join tables A Primary Key is a unique identifier for a row in a table. It ensures that each record in the table can be uniquely identified. A Foreign Key is a field in one table that refers to the **Primary Key** of another table. It establishes a relationship between the two tables. Let's consider two tables: **Company** and **Employee**. Here the `Company ID` serves as the **Primary Key**, ensuring each company has a unique identifier. | Company ID (PK) | Company Name | Product | Location | | --- | --- | --- | --- | | 1 | Tech Corp | Software | USA | | 2 | InnovateX | AI Tools | Germany | And here the `Company ID` is a **Foreign Key**, linking each employee to a company from the **Company Table**. | Employee Name | Employee Position | Company ID (FK) | | --- | --- | --- | | Alice | Developer | 1 | | Bob | Manager | 2 | In a **many-to-many relationship**, a single record in one table can be associated with multiple records in another table, and vice versa. Since relational databases do not support direct many-to-many relationships, we use a **join table** (also called a **junction table**) to bridge the two tables. Consider the following tables: * **Student**: Stores information about students (e.g., name, major, and ID as the primary key). * **Courses**: Stores information about courses (e.g., subject, duration, and ID as the primary key). To track which students are enrolled in which courses, we create a **join table** that contains foreign keys referencing both tables. `CREATE TABLE student_course ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES Student(id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES Courses(id) ON DELETE CASCADE );` To retrieve the names of students who have taken a specific course (e.g., _Computer Science_), use the following query: `SELECT Student.name FROM Student JOIN student_course ON Student.id = student_course.student_id JOIN Courses ON Courses.id = student_course.course_id WHERE Courses.subject = 'Computer Science';` **Table Summary** | Relationship | Example | Schema | Foreign key placement | | --- | --- | --- | --- | | One to One (1:1) | Person → Passport | passports.person\_id UNIQUE | Foreign key in one table, set as unique. | | One-to-Many (1:M) | Company → Employees | employees.company\_id | Foreign key in the child table (employees). | | Many-to-Many (M:M) | Students ↔ Courses | enrollments(student\_id, course\_id) | Join table with two foreign keys. | ##### Indexes, Race conditions and SQL injections attacks **Indexes** Indexes create a B-tree structure (not binary tree structure) that allows us to access information faster. They improve performance a lot when we want to read data from the database but might slow down the insertion and deletion ( as we saw with tree like structure that need to stay balanced). So they are great for read heavy sites. **Race conditions** Imagine a picture goes viral and everyone in the world wants to like it at the same time. How can we make sure we can hanlde the database update in a way that data is not lost? So there are many techniques to handle this problem but we will focus on one in particular which is transcations and pessimistic locking. In our example, the transaction will group the reading and the updating of the likes in one operation and the locking will make sure that one transaction is executed at a time. For more on the topic look ACID properties in database **SQL Injection Attacks** SQL Injection Attacks are malicious inputs that could give access to unauthorized information. If your API is using f format and not placeholders, a user could enter as username : name'-- The ' would close the first string and the -- would ignore everything after this command. So basically I user could access the account of someone by only entering the username and not the password. So use the placeholder instead of the f string. rows = db.execute (f"SELECT \* FROM users WHERE username = '{username}' AND password = '{password}'") // Bad rows = db.execute ("SELECT \* FROM users WHERE username = ? AND password = ? ", username , password) // Better